
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spBulkOrdersAvailabilityReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spBulkOrdersAvailabilityReport]
GO

	
	
CREATE PROCEDURE spBulkOrdersAvailabilityReport
@conPointID int,
@supplier varchar(3),
@owner int,
@type int,
@target int,
@both int,
@qty int,
@IsUSUser bit
AS

SELECT
	[BulkOrders].[BulkOrderID],
	[OrderDate],
	[Type],
	[ProductCode],
	[Description],
	[Color],
	[StartDate],
	[TotalQtyOrdered],
	[TotalQtyCompleted],
	[TotalQtyInProgressBelow50P],
	[TotalQtyInProgressAbove50P],
	[EstimatedDate],
	CASE [Type]
		WHEN 1 THEN
			([TotalQtyOrdered] - ([TotalQtyCompleted]+ISNULL([TotalQtyInProgressBelow50P],0)+ISNULL([TotalQtyInProgressAbove50P], 0)))
		ELSE
			NULL
	END NotStarted,
	[StockLastCheckedDate],
	[StockLastCheckedQty],
	[FOB],
	[CompletedDate],
	[BulkOrders].[SupplierCode],
	[Owner],
	[Catalogue],
	[Page],
	[Pack],
	[PackQty],
	[CatalogID],
	[Target],
	[SalesDescription1],
	[SalesDescription2],
	[StockLocation],
	[AvailableInUSA],
	[BONo],
	[CatPackDesc],
	[MinOrderQty],
	[TallPaletQty],
	(TotalQtyOrdered) as ActualStock,
	([TotalQtyCompleted] - ISNULL((SELECT SUM(QtyIssued)
					FROM DeliveredOrders 
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]), 0)) as QuantityAvailable
FROM
	[BulkOrders] LEFT JOIN Suppliers ON [BulkOrders].SupplierCode = Suppliers.SupplierCode
WHERE  (@ConPointID = -1 OR Suppliers.ConPointID = @ConPointID)
AND (@supplier = '' OR @supplier = [BulkOrders].SupplierCode)
AND ((@both = 0 AND CompletedDate IS NULL) OR @both=1)
AND (@owner = -1 OR @owner = owner)
AND (@type = -1 OR (@type = 4 AND (type=2 OR type=3)) OR @type = type)
AND (@target = -1 OR ISNULL(target, 0) = @target)
AND (([TotalQtyCompleted] - ISNULL((SELECT SUM(QtyIssued)
					FROM DeliveredOrders 
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]), 0)) > @qty)
AND (@IsUSUser = 0 OR (@IsUSUser=1 AND [AvailableInUSA] = 1))

ORDER BY ProductCode, Owner

GO

	
